﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    
    public partial class form_main : Form
    {
        DataSet ds = new DataSet();
        OleDbDataAdapter da;

        public form_main()
        {
            InitializeComponent();
        }

        private void form_main_Load(object sender, EventArgs e)
        {
           connect_to_db();
        }

        private void connect_to_db()
        {
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
            string q = "SELECT Код, [Название эксперимента], Установка, [Вид анализа], Проба, Дата, Комментарий FROM table_main";

            da = new OleDbDataAdapter(q, conn);
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds, "table_main");
            grid_main.DataSource = ds.Tables["table_main"];
            grid_main.Columns[0].Visible = false;
        }

        public void Add_new_experiment(String Name_experiment, String Ustanovka, String Type_analysis, String Sample, DateTime Date_experiment, String Comment)
        {
            connect_to_db();

            DataRow row = ds.Tables["table_main"].NewRow();
            row["Название эксперимента"] = Name_experiment;
            row["Установка"] = Ustanovka;
            row["Вид анализа"] = Type_analysis;
            row["Проба"] = Sample;
            row["Дата"] = Date_experiment;
            row["Комментарий"] = Comment;
            ds.Tables["table_main"].Rows.Add(row);

            OleDbCommand InsertCommand = new OleDbCommand();
            InsertCommand.CommandText = @"INSERT INTO [table_main] ([Название эксперимента],[Установка],[Вид анализа],[Проба],[Дата],[Комментарий]) VALUES('" + Name_experiment + "','" + Ustanovka + "','" + Type_analysis + "','" + Sample + "','" + Date_experiment + "','" + Comment + "')";
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
            InsertCommand.Connection = conn;
            da.InsertCommand = InsertCommand;
            da.Update(ds, "table_main");
        }

        private void btn_add_new_record_Click(object sender, EventArgs e)
        {
            form_add_new_record form_add_new_record = new form_add_new_record();
            form_add_new_record.Owner = this;
            form_add_new_record.Show();
        }

        private void btn_delete_record_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Удалить эксперимент '" + grid_main[1, grid_main.CurrentRow.Index].Value.ToString() + "' ?", "PAV: Удаление эксперимента", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
                OleDbCommand myOleDbCommand = conn.CreateCommand();
                myOleDbCommand.CommandText = @"DELETE FROM [table_main] WHERE (Код = " + grid_main[0, grid_main.CurrentRow.Index].Value.ToString() + ");";
                conn.Open();
                myOleDbCommand.ExecuteNonQuery();
                conn.Close();
                myOleDbCommand = conn.CreateCommand();
                myOleDbCommand.CommandText = @"DELETE FROM [table_dependence] WHERE ([Номер эксперимента] = " + grid_main[0, grid_main.CurrentRow.Index].Value.ToString() + ");";
                conn.Open();
                myOleDbCommand.ExecuteNonQuery();
                conn.Close();

                ds = new DataSet();
                connect_to_db();
            }
        }

        private void btn_refresh_Click(object sender, EventArgs e)
        {
            ds = new DataSet();
            connect_to_db();
        }

        private void btn_filter_Click(object sender, EventArgs e)
        {
            connect_to_db();

            string filter_conditions = "";
            if (cmb_filter_ustanovka.Text != "")
                filter_conditions = "Установка Like '" + cmb_filter_ustanovka.Text + "'";

            if (cmb_filter_type_analysis.Text != "")
            {
                if (filter_conditions != "")
                {
                    filter_conditions += " and ";
                }
                filter_conditions += "[Вид анализа] Like '" + cmb_filter_type_analysis.Text + "'";
            }
            if (chb_filter_Date_experiment.Checked == true)
            {
                if (filter_conditions != "")
                {
                    filter_conditions += " and ";
                }
                filter_conditions += "[Дата] <= '" + dat_filter_Date_experiment.Value + "'";
            }

            BindingSource binding = new BindingSource();
            binding.DataSource = ds.Tables["table_main"];
            binding.Filter = filter_conditions;
        }

        private void cmb_filter_ustanovka_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmb_filter_ustanovka.Text != "")
            {
                btn_filter_ustanovka_clear.Enabled = true;
            }
            else
            {
                btn_filter_ustanovka_clear.Enabled = false;
            }
        }

        private void btn_filter_ustanovka_clear_Click(object sender, EventArgs e)
        {
            cmb_filter_ustanovka.Text = "";
            btn_filter_ustanovka_clear.Enabled = false;
        }

        private void cmd_filter_type_analysis_clear_Click(object sender, EventArgs e)
        {
            cmb_filter_type_analysis.Text = "";
            cmd_filter_type_analysis_clear.Enabled = false;
        }

        private void cmb_filter_type_analysis_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmb_filter_type_analysis.Text != "")
            {
                cmd_filter_type_analysis_clear.Enabled = true;
            }
            else
            {
                cmd_filter_type_analysis_clear.Enabled = false;
            }
        }

        private void chb_filter_Date_experiment_CheckedChanged(object sender, EventArgs e)
        {
            dat_filter_Date_experiment.Enabled = chb_filter_Date_experiment.Checked;
        }

        private void cmd_filterpanel_Click(object sender, EventArgs e)
        {
            if (filter_panel.Visible)
                filter_panel.Visible = false;
            else
                filter_panel.Visible = true;
            
        }

        private void grid_main_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            string number_experiment = grid_main[0, grid_main.SelectedCells[0].RowIndex].Value.ToString();
            string name_experiment = grid_main[1, grid_main.SelectedCells[0].RowIndex].Value.ToString();
            string ustanovka = grid_main[2, grid_main.SelectedCells[0].RowIndex].Value.ToString();
            string type_analysis = grid_main[3, grid_main.SelectedCells[0].RowIndex].Value.ToString();
            string sample = grid_main[4, grid_main.SelectedCells[0].RowIndex].Value.ToString();
            string date_experiment = grid_main[5, grid_main.SelectedCells[0].RowIndex].Value.ToString();

            form_dependence1 form_dependence = new form_dependence1();
            form_dependence.Owner = this;
            form_dependence.Show();


            if (form_dependence != null)
            {
                form_dependence.Show_dependence(number_experiment, name_experiment, ustanovka, type_analysis, sample, date_experiment);
            }
        }


    }
}
